#  -*-coding:utf8 -*-
import os

import sqlalchemy
from flask import Flask, render_template, redirect, url_for, request, session
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import or_, func, and_, distinct

app = Flask(__name__)
db = SQLAlchemy(app)
app.config['SECRET_KEY'] = os.urandom(24)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:123456@127.0.0.1:3306/itman'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True


class Users(db.Model):
    __tablename__ = 'Users'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    username = db.Column(db.String(20), nullable=False)
    password = db.Column(db.String(50), nullable=False)
    name = db.Column(db.String(20))
    roles = db.Column(db.String(30))
    other = db.Column(db.String(50))


class devices(db.Model):
    __tablename__ = 'devices'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    status = db.Column(db.String(20), nullable=False)
    hostname = db.Column(db.String(50))
    zone = db.Column(db.String(100))
    locational = db.Column(db.String(100))
    detail = db.Column(db.String(100))
    type = db.Column(db.String(100))
    modes = db.Column(db.String(100))
    os = db.Column(db.String(100))
    lanip = db.Column(db.String(100))
    wanip = db.Column(db.String(100))
    enduser = db.Column(db.String(100))
    app = db.Column(db.String(100))
    supportinfo = db.Column(db.String(100))
    assettag = db.Column(db.String(100))
    logininfo = db.Column(db.String(200))
    macadd = db.Column(db.String(100))
    other = db.Column(db.String(100))


class logs(db.Model):
    __tablename__ = 'logs'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    user = db.Column(db.String(100), nullable=False)
    actiontype = db.Column(db.String(100))
    datetime = db.Column(db.String(100))
    actionlogs = db.Column(db.String(100))


class systems(db.Model):
    __tablename__ = 'systems'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    status = db.Column(db.String(20), nullable=False)
    locational = db.Column(db.String(100))
    zone = db.Column(db.String(100))
    type = db.Column(db.String(100))
    os = db.Column(db.String(100))


@app.route('/login', methods=['POST', 'GET'])
def login():
    if request.method == 'GET':
        return render_template('login.html')
        cooickname = session.get('username')
        if cooickname:
            return redirect(url_for('index'))
        else:
            return redirect(url_for(login))
    else:
        username = request.form.get('username')
        password = request.form.get('password')
        dbuser = Users.query.filter(Users.username == username, Users.password == password).first()
        if dbuser:
            session["username"] = username
            return redirect(url_for('index'))
        else:
            return "用户不存在或者用户名密码错误"


@app.route('/index', methods=['POST', 'GET'])
def index():
    if request.method == 'GET':
        username = session.get('username')
        if username:
            deviceslist = devices.query.limit(15).all()
            servercounts = int(db.session.query(sqlalchemy.func.count(distinct(devices.type == "物理服务器"))).scalar(), )
            vmcounts = int(db.session.query(sqlalchemy.func.count(distinct(devices.type == "虚拟机"))).scalar(), )
            gwcounts = int(db.session.query(sqlalchemy.func.count(distinct(devices.type == "网关类"))).scalar(), )
            swcounts = int(db.session.query(sqlalchemy.func.count(distinct(devices.type == "网关类"))).scalar(), )
            othernetk = int(db.session.query(sqlalchemy.func.count(distinct(devices.type == "其它网络设备"))).scalar(), )
            totalnetcon = gwcounts + swcounts + othernetk
            toptalsercon = vmcounts + servercounts
            # return render_template('server.html')
            return render_template('index.html', deviceslist=deviceslist, totalnetcon=totalnetcon,
                                   toptalsercon=toptalsercon)
        else:
            return redirect(url_for('login'))


@app.route('/delete/<int:id>')
def delete(id):
    cooickname = session.get('username')
    if cooickname:
        devices.query.filter_by(id=id).delete()
        db.session.commit()
        return redirect(url_for('index'))
    else:
        return redirect(url_for(login))


@app.route('/update/<int:id>', methods=['POST', 'GET'])
def edit(id):
    if request.method == 'GET':
        cooickname = session.get('username')
        if cooickname:
            updata1 = devices.query.filter_by(id=id).all()
            return render_template('edit.html', deviceslist=updata1)
        else:
            return redirect(url_for(login))
    else:
        dic1 = request.form
        save(dic1)
        return redirect(url_for("index"))


@app.route('/logout')
def logout():
    username = session.get('username')
    if username:
        session.pop('username')
        return redirect(url_for('login'))
    else:
        return render_template('login.html')


@app.route('/chs', methods=['POST', 'GET'])
def chs():
    if request.method == 'GET':
        username = session.get('username')
        if username:
            dbnamedt = Users.query.filter(Users.username == username).first()
            name = dbnamedt.name
            return render_template("chpaswd.html", username=username, name=name)
        return redirect(url_for('login'))
    else:
        getusername = session.get('username')
        passwd1 = request.form.get('password1')
        passwd2 = request.form.get('password2')
        if passwd1 != passwd2:
            return u'你两次输入的密码不一致'
        else:
            dbpass = Users.query.filter(Users.username == getusername).first()
            dbpass.password = passwd1
            db.session.commit()
            return u'密码改成功'


@app.route('/get/')
def get():
    username = session.get('username')
    return "get cookice"


@app.route('/search/', methods=['POST', 'GET'])
def search():
    cooickname = session.get('username')
    if cooickname:
        q = request.args.get('q')
        searchdatas = devices.query.filter(or_(devices.status.contains(q), devices.hostname.contains(q),
                                               devices.assettag.contains(q), devices.locational.contains(q),
                                               devices.os.contains(q), devices.macadd.contains(q),
                                               devices.detail.contains(q), devices.type.contains(q),
                                               devices.zone.contains(q), devices.modes.contains(q),
                                               devices.modes.contains(q), devices.os.contains(q),
                                               devices.lanip.contains(q),
                                               devices.wanip.contains(q), devices.enduser.contains(q),
                                               devices.app.contains(q),
                                               devices.supportinfo.contains(q), devices.logininfo.contains(q),
                                               devices.macadd.contains(q),
                                               devices.other.contains(q)))
        return render_template('index.html', deviceslist=searchdatas)
    else:
        return redirect(url_for('login'))


@app.route('/server', methods=['GET'])
def server():
    username = session.get('username')
    if username:
        servercounts = int(db.session.query(sqlalchemy.func.count(distinct(devices.type == "物理服务器"))).scalar(), )
        vmcounts = int(db.session.query(sqlalchemy.func.count(distinct(devices.type == "虚拟机"))).scalar(), )
        gwcounts = int(db.session.query(sqlalchemy.func.count(distinct(devices.type == "网关类"))).scalar(), )
        swcounts = int(db.session.query(sqlalchemy.func.count(distinct(devices.type == "网关类"))).scalar(), )
        othernetk = int(db.session.query(sqlalchemy.func.count(distinct(devices.type == "其它网络设备"))).scalar(), )
        totalnetcon = gwcounts + swcounts + othernetk
        toptalsercon = vmcounts + servercounts
        serverdata = devices.query.filter(or_(devices.type == '物理服务器', devices.type == '虚拟机',
                                              devices.type == '存储类')).all()
        return render_template('index.html', deviceslist=serverdata)
    else:
        return redirect(url_for('login'))


@app.route('/networks', methods=['POST', 'GET'])
def networks():
    if request.method == 'GET':
        username = session.get('username')
        if username:
            servercounts = int(db.session.query(sqlalchemy.func.count(distinct(devices.type == "物理服务器"))).scalar(), )
            vmcounts = int(db.session.query(sqlalchemy.func.count(distinct(devices.type == "虚拟机"))).scalar(), )
            gwcounts = int(db.session.query(sqlalchemy.func.count(distinct(devices.type == "网关类"))).scalar(), )
            swcounts = int(db.session.query(sqlalchemy.func.count(distinct(devices.type == "网关类"))).scalar(), )
            othernetk = int(db.session.query(sqlalchemy.func.count(distinct(devices.type == "其它网络设备"))).scalar(), )
            totalnetcon = gwcounts + swcounts + othernetk
            toptalsercon = vmcounts + servercounts
            networksdata = devices.query.filter(
                or_(devices.type == '网关类', devices.type == '其它网络设备', devices.type == '交换机')).all()
            return render_template('index.html', deviceslist=networksdata)
        else:
            return redirect(url_for('login'))


@app.route('/pc', methods=['POST', 'GET'])
def pc():
    if request.method == 'GET':
        username = session.get('username')
        if username:
            pcdata = devices.query.filter(or_(devices.type == '台式机', devices.type == '笔记本',
                                              devices.type == '瘦客户机')).all()
            connts()
            return render_template('index.html', deviceslist=pcdata)
        else:
            return redirect(url_for('login'))


@app.route('/other', methods=['POST', 'GET'])
def other():
    if request.method == 'GET':
        username = session.get('username')
        if username:
            otherdata = devices.query.filter(devices.type == '其它设备').all()
            return render_template('index.html', deviceslist=otherdata)
        else:
            return redirect(url_for('login'))


@app.route('/add', methods=['POST', 'GET'])
def add():
    if request.method == 'GET':
        username = session.get('username')
        if username:
            return render_template('add.html')
        else:
            return redirect(url_for('login'))
    else:
        dic1 = request.form
        save(dic1)
        return redirect(url_for('index'))


def check(username):
    username = session.get('username')
    if username:
        pass
    else:
        return redirect(url_for('login'))


def connts():
    deviceslist = devices.query.limit(15).all()


def save(dic1):
    if "id" not in dic1:
        adddata1 = devices(status=dic1['status'], hostname=dic1['hostname'], assettag=dic1['assettag'],
                           locational=dic1['locational'], detail=dic1['detail'], zone=dic1['zone'], type=dic1['type'],
                           modes=dic1['modes'], os=dic1['os'], lanip=dic1['lanip'], wanip=dic1['lanip'],
                           enduser=dic1['enduser'], app=dic1['apps'], supportinfo=dic1['supportinfo'],
                           logininfo=dic1['logininfo'], macadd=dic1['macadd'], other=dic1['other'])
        db.session.add(adddata1)
        db.session.commit()
    else:
        devices.query.filter_by(id=dic1['id']).update({'status': dic1['status'], 'hostname': dic1['hostname'],
                                                       'assettag': dic1['assettag'], 'locational': dic1['locational'],
                                                       'zone': dic1['zone'], 'detail': dic1['detail'],
                                                       'type': dic1['type'], 'modes': dic1['modes'],
                                                       'lanip': dic1['lanip'], 'wanip': dic1['wanip'],
                                                       'enduser': dic1['enduser'], 'app': dic1['apps'],
                                                       'supportinfo': dic1['supportinfo'],
                                                       'logininfo': dic1['logininfo'], 'macadd': dic1['macadd'],
                                                       'other': dic1['other']})
        db.session.commit()


if __name__ == '__main__':
    app.run(host='0.0.0.0', port=80, debug=True)
